/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package com.moedik.model;

import com.moedik.classes.Flight;
import com.moedik.classes.FlightAirline;
import com.moedik.classes.FlightAirport;
import com.moedik.classes.FlightBook;
import com.moedik.classes.FlightClass;
import com.moedik.classes.Kalender;
import com.moedik.classes.MailSender;
import com.moedik.classes.User;
import java.security.SecureRandom;
import java.sql.PreparedStatement;
import java.sql.Statement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.logging.Level;
import java.util.logging.Logger;

/**
 *
 * @author LegACy
 */
public class FlightModel extends Model {

    public FlightModel() {
        super();
    }

    public ArrayList<Flight> getFlights() {
        //Set default
        ArrayList<Flight> Result = new ArrayList<Flight>();

        try {
            //Create data list
            ArrayList<FlightClass> Classes = new ArrayList<FlightClass>();
            ArrayList<FlightAirport> Airports = new ArrayList<FlightAirport>();
            ArrayList<FlightAirline> Airlines = new ArrayList<FlightAirline>();

            //Get data
            ResultSet SQLClasses = con.createStatement().executeQuery("SELECT * FROM flight_classes");
            ResultSet SQLAirports = con.createStatement().executeQuery("SELECT * FROM flight_airports");
            ResultSet SQLAirlines = con.createStatement().executeQuery("SELECT * FROM flight_airlines");
            while (SQLClasses.next()) {
                Classes.add(new FlightClass(SQLClasses.getInt(FlightClass.FIELD_ID), SQLClasses.getString(FlightClass.FIELD_NAME)));
            }
            while (SQLAirlines.next()) {
                Airlines.add(new FlightAirline(SQLAirlines.getInt(FlightAirline.FIELD_ID), SQLAirlines.getString(FlightAirline.FIELD_NAME)));
            }
            while (SQLAirports.next()) {
                Airports.add(new FlightAirport(SQLAirports.getInt(FlightAirport.FIELD_ID),
                        SQLAirports.getInt(FlightAirport.FIELD_CITY),
                        SQLAirports.getString(FlightAirport.FIELD_NAME)));
            }

            //TODO flight only after today (don't show past flights)

            //Get all flights
            ResultSet SQL = con.createStatement().executeQuery("SELECT * FROM flights");
            while (SQL.next()) {
                Result.add(new Flight(SQL.getInt(Flight.FIELD_ID),
                        SQL.getInt(Flight.FIELD_ORIGIN),
                        SQL.getInt(Flight.FIELD_DESTINATION),
                        SQL.getInt(Flight.FIELD_AIRLINE),
                        SQL.getInt(Flight.FIELD_CLASS),
                        SQL.getInt(Flight.FIELD_FARE),
                        SQL.getLong(Flight.FIELD_TIME)));
            }

            //For each flight
            for (int i = 0; i < Result.size(); i++) {
                //Set data
                Result.get(i).setClass(Classes.get(Result.get(i).getClassID() - 1).getName());
                Result.get(i).setOrigin(Airports.get(Result.get(i).getOriginID() - 1).getName());
                Result.get(i).setAirline(Airlines.get(Result.get(i).getAirlineID() - 1).getName());
                Result.get(i).setDestination(Airports.get(Result.get(i).getDestinationID() - 1).getName());
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        //Return
        return Result;
    }

    public ArrayList<Flight> getServices(int airport_id, long date) {
        Kalender kal = new Kalender(date);
        String da = kal.getYear() + "-" + String.format("%02d", kal.getMonth() + 1) + "-" + String.format("%02d", kal.getDate());
        ArrayList<Flight> flights = new ArrayList<Flight>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT f.id,fad.name,fa.name,fc.name,f.fare,f.time FROM flights as f,flight_airports as fad,flight_airlines as fa,flight_classes as fc WHERE f.origin_id = " + airport_id + " AND f.destination_id = fad.id AND f.airline_id=fa.id AND f.class_id=fc.id AND f.time LIKE '%" + da + "%'";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Flight flight = new Flight();
                flight.setID(rs.getInt(1));
                flight.setDestination(rs.getString(2));
                flight.setAirline(rs.getString(3));
                flight.setClass(rs.getString(4));
                flight.setFare(rs.getInt(5));
                flight.setTimeStr(rs.getString(6));
                flights.add(flight);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return flights;
    }

    public ArrayList<FlightAirport> getAirport() {
        ArrayList<FlightAirport> airports = new ArrayList<FlightAirport>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT fa.id,fa.name,c.name FROM flight_airports as fa, cities as c WHERE c.id=fa.city_id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                FlightAirport airport = new FlightAirport();
                airport.setID(rs.getInt(1));
                airport.setName(rs.getString(2));
                airport.setCityName(rs.getString(3));
                airports.add(airport);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return airports;
    }

    public void deleteService(int schedId) {
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM flights WHERE id = " + schedId;
            stmt.executeUpdate(query);
            this.lm.log("layanan flight "+schedId+" telah dihapus");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public Flight getService(int serviceId) {
        Flight flight = new Flight();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT DISTINCT f.id,fad.name,fa.name,fc.name,f.fare,f.time FROM flights as f,flight_airports as fad,flight_airlines as fa,flight_classes as fc WHERE f.id = '" + serviceId + "' AND f.destination_id = fad.id AND f.airline_id=fa.id AND f.class_id=fc.id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                flight.setID(rs.getInt(1));
                flight.setDestination(rs.getString(2));
                flight.setAirline(rs.getString(3));
                flight.setClass(rs.getString(4));
                flight.setFare(rs.getInt(5));
                flight.setTimeStr(rs.getString(6));
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return flight;
    }

    public ArrayList<FlightAirport> getAirports() {
        ArrayList<FlightAirport> airports = new ArrayList<FlightAirport>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM flight_airports";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                FlightAirport airport = new FlightAirport();
                airport.setID(rs.getInt(1));
                airport.setName(rs.getString(2));
                airports.add(airport);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return airports;
    }

    public ArrayList<FlightAirline> getAirlines() {
        ArrayList<FlightAirline> airlines = new ArrayList<FlightAirline>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM flight_airlines";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                FlightAirline airline = new FlightAirline();
                airline.setID(rs.getInt(1));
                airline.setName(rs.getString(2));
                airlines.add(airline);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return airlines;
    }

    public ArrayList<FlightClass> getClasses() {
        ArrayList<FlightClass> classes = new ArrayList<FlightClass>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM flight_classes";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                FlightClass fclass = new FlightClass();
                fclass.setID(rs.getInt(1));
                fclass.setName(rs.getString(2));
                classes.add(fclass);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return classes;
    }

    public void addService(int airportFrom, int airportTo, int airlineId, int airlineClass, int fare, String date, String hour, String minute) {
        PreparedStatement stmt = null;
        String time = date + " " + hour + ":" + minute + ":00";
        try {
            String query = "INSERT INTO flights (origin_id, destination_id, airline_id, class_id, fare, time) VALUES (" + airportFrom + ", " + airportTo + ", " + airlineId + "," + airlineClass + "," + fare + ",'" + time + "') ";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public void updateService(int schedId, int airportFrom, int airportTo, int airlineId, int airlineClass, int fare, String date, String hour, String minute) {
        PreparedStatement stmt = null;
        String time = date + " " + hour + ":" + minute + ":00";
        try {
            String query = "UPDATE flights SET origin_id=" + airportFrom + ", destination_id=" + airportTo + ", airline_id=" + airlineId + ", class_id=" + airlineClass + ", fare=" + fare + ", time='" + time + "' WHERE id=" + schedId;
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            this.lm.log("layanan flight "+schedId+" telah diperbaharui");
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public String book(int user_id, int service_id, String date) {
        String bookingCode = null;

        String chars = "ABCDEFGHIJKLMNOPQRSTUVWXYZ0123456789";
        char[] buf = new char[6];
        SecureRandom r = new SecureRandom();

        for (int i = 0; i < buf.length; i++) {
            buf[i] = chars.charAt(r.nextInt(chars.length()));
        }

        String code = new String(buf);

        try {
            PreparedStatement stmt = null;
            ResultSet rs = null;

            String query = "INSERT INTO flight_books (id, user_id, flight_service_id, date, code) VALUES (NULL, '" + user_id + "', '" + service_id + "', '" + date + "', '" + code + "')";
            stmt = con.prepareStatement(query, Statement.RETURN_GENERATED_KEYS);
            stmt.executeUpdate();
            rs = stmt.getGeneratedKeys();

            rs.next();

            bookingCode = code;
            LogModel lm = new LogModel();
            lm.bookFlight(user_id, service_id);
        } catch (SQLException ex) {
            Logger.getLogger(ShuttleModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        String billno = null;

        if (bookingCode != null) {
            PaymentModel paymentModel = new PaymentModel();
            billno = paymentModel.register(user_id, 3, date, bookingCode);

            UserModel userModel = new UserModel();
            User user = userModel.get(user_id);

            FlightModel flightModel = new FlightModel();
            Flight flight = flightModel.getService(service_id);

            ConfigModel configModel = new ConfigModel();
            String companyName = configModel.getCompanyName();

            int fare = flight.getFare();

            String[] recipient = {user.getEmail()};
            String body = "Yth. " + user.getName() + "\n\nTerima kasih atas pemesanan Anda.\n\nNomor pemesanan Anda adalah: " + billno + "\n\nTotal pembayaran Anda adalah: Rp. " + fare + ",-\n\nAnda dapat melakukan pembayaran melalui transfer ke rekening berikut:\n\nBank Mandiri Cabang Bandung Siliwangi\na.n. Samudra Harapan Bekti\nNo. Rek. 1300005229169\n\nSegera lakukan konfirmasi setelah Anda melakukan pembayaran agar dapat kami proses.\n\nTerima kasih.\n\n---\n" + companyName;
            MailSender.mail(recipient, "[" + companyName + "] Your Booking Information", body);

        }

        return billno;
    }

    public ArrayList<Integer> getReport() {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM payments WHERE date between '"+first+"' AND '"+last+"' AND service=3";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public void deleteFlightBook(int id) {
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "DELETE FROM flight_books WHERE id = '" + id + "'";
            stmt.executeUpdate(query);
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public ArrayList<FlightBook> getFlightBooks() {
        ArrayList<FlightBook> flightBooks = new ArrayList<FlightBook>();
        Statement stmt = null;

        try {
            stmt = con.createStatement();
            String query = "SELECT * FROM flight_books,flights WHERE flights.id=flight_service_id";
            ResultSet rs = stmt.executeQuery(query);

            while (rs.next()) {
                Statement stmt2 = con.createStatement();
                String query2 = "SELECT username FROM users WHERE id=" + rs.getInt("user_id");
                ResultSet rs2 = stmt2.executeQuery(query2);
                rs2.next();
                
                Statement stmt3 = con.createStatement();
                String query3 = "SELECT * FROM flight_airports,cities WHERE city_id=cities.id AND flight_airports.id=" + rs.getInt("origin_id");
                ResultSet rs3 = stmt3.executeQuery(query3);
                rs3.next();
                
                Statement stmt4 = con.createStatement();
                String query4 = "SELECT * FROM flight_airports,cities WHERE city_id=cities.id AND flight_airports.id=" + rs.getInt("destination_id");
                ResultSet rs4 = stmt4.executeQuery(query3);
                rs4.next();

                int id = rs.getInt("id");
                String username = rs2.getString("username");
                String originCity = rs3.getString("cities.name");
                String originPool = rs3.getString("flight_airports.name");
                String destinationCity = rs4.getString("cities.name");
                String destinationPool = rs4.getString("flight_airports.name");
                String shuttleClass = "";
                String date = rs.getString("date");
                String code = rs.getString("code");

                FlightBook flightBook = new FlightBook(id, username, originCity, originPool, destinationCity, destinationPool, shuttleClass, date, code);
                flightBooks.add(flightBook);
            }
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }

        return flightBooks;
    }

    public ArrayList<Integer> getReport(int year) {
        ArrayList<Integer> result = new ArrayList<Integer>();
        Statement stmt = null;
        int count;
        try {
            Calendar cal = Calendar.getInstance();
            cal.set(Calendar.YEAR, year);
            String first,last;
            for(int i=0;i<12;i++){
                cal.set(Calendar.MONTH, i);
                cal.set(Calendar.DATE, 1);
                first = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-01";
                cal.add(Calendar.MONTH, 1);
                cal.add(Calendar.DATE, -1);
                last = cal.get(Calendar.YEAR)+"-"+(cal.get(Calendar.MONTH)+1)+"-"+cal.get(Calendar.DATE);
                
                stmt = con.createStatement();
                String query = "SELECT COUNT(id) FROM payments WHERE date between '"+first+"' AND '"+last+"' AND service=3";
                ResultSet rs = stmt.executeQuery(query);
                rs.next();
                count = rs.getInt(1);
                result.add(count);
            }
            
        } catch (SQLException ex) {
            Logger.getLogger(ConfigModel.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }
}
